package DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.swing.JFormattedTextField;
import POJO.AlunoEtec;
import POJO.AlunoFatec;
import POJO.Funcionario;
import POJO.Pessoa;
import POJO.Professor;


public class PessoaDAO {
	Connection c;
	
	
	public PessoaDAO() {
		GenericDao gDao = new GenericDao();
		c = gDao.getConnection();
	}

	
	public void cadastrarPessoa(Pessoa p, JFormattedTextField Celular,
			JFormattedTextField Res) throws SQLException {
		String sql ="";
		
		sql = "INSERT INTO Pessoa (rg, nome, rua, numero, email, complemento) VALUES(?, ?, ?, ?, ?, ?)";

		PreparedStatement ps = c.prepareStatement(sql);

		ps.setString(1, p.getRg());
		ps.setString(2, p.getNome());
		ps.setString(3, p.getRua());
		ps.setString(4, p.getNumero());
		ps.setString(5, p.getEmail());
		ps.setString(6, p.getComplemento());
		ps.execute();
		ps.close();

		String telefone = "INSERT INTO Telefone (rg, tipoTelefone, numeroTelefone)VALUES (?,?,?)";

		if (!Res.getText().equals("")) {
			ps = c.prepareStatement(telefone);

			ps.setString(1, p.getRg());
			ps.setString(2, "Residencial");
			ps.setString(3, p.getNumeroResidencia());
			ps.execute();
			ps.close();
		}
		if (!Celular.getText().equals("")) {
			ps = c.prepareStatement(telefone);

			ps.setString(1, p.getRg());
			ps.setString(2, "Celular");
			ps.setString(3, p.getNumeroCelular());
			ps.execute();
			ps.close();
		}

		if (p instanceof AlunoFatec) 
		{
			sql = "INSERT INTO AlunoFatec (rg, ra) VALUES (?, ?)";
			ps = c.prepareStatement(sql);
			ps.setString(1, p.getRg());
			ps.setString(2, ((AlunoFatec) p).getRa());
			ps.execute();
			ps.close();
		}
		else if (p  instanceof AlunoEtec)
		{
			sql = "INSERT INTO AlunoEtec (rg, rm) VALUES (?, ?)";
			ps = c.prepareStatement(sql);
			ps.setString(1, p.getRg());
			ps.setString(2, ((AlunoEtec) p).getRm());			
			ps.execute();
			ps.close();
		}
		else if(p instanceof Funcionario)
		{
			sql = "INSERT INTO Funcionario (rg, codSetor) VALUES (?, ?)";
			ps = c.prepareStatement(sql);
			ps.setString(1, p.getRg());
			ps.setInt(2, ((Funcionario) p).getCodSetor());			
			ps.execute();
			ps.close();				
		}
		else if (p instanceof Professor)
		{
			sql = "INSERT INTO Professor (rg, registro) VALUES (?, ?)";
			ps = c.prepareStatement(sql);
			ps.setString(1, p.getRg());
			ps.setString(2, ((Professor) p).getRegistro());			
			ps.execute();
			ps.close();
		}		
	}
	
	public void atualizaPessoa(Pessoa p) throws SQLException{
		
		String sql="";
		
		sql = "UPDATE Pessoa SET nome = ?, rua = ?, numero = ?, email = ?, complemento = ? WHERE rg = ?";
		
		PreparedStatement ps = c.prepareStatement(sql); 
		
		ps.setString(1, p.getNome());
		ps.setString(2, p.getRua());
		ps.setString(3, p.getNumero());
		ps.setString(4, p.getEmail());
		ps.setString(5, p.getComplemento());
		ps.setString(6, p.getRg());
		ps.execute();
		ps.close();
		
		if (p instanceof AlunoFatec) 
		{
			sql = "UPDATE AlunoFatec SET ra = ? WHERE rg = ?";
			ps = c.prepareStatement(sql);
			ps.setString(1, ((AlunoFatec) p).getRa());
			ps.setString(2, p.getRg());
			ps.execute();
			ps.close();
		}
		else if (p  instanceof AlunoEtec)
		{
			sql = "UPDATE AlunoEtec SET rm = ? WHERE rg = ?";
			ps = c.prepareStatement(sql);
			ps.setString(1, ((AlunoEtec) p).getRm());
			ps.setString(2, p.getRg());
			ps.execute();
			ps.close();
		}
		else if(p instanceof Funcionario)
		{
			sql = "UPDATE Funcionario SET codSetor = ? WHERE rg = ?";
			ps = c.prepareStatement(sql);
			ps.setInt(1, ((Funcionario) p).getCodSetor());
			ps.setString(2, p.getRg());
			ps.execute();
			ps.close();				
		}
		else if (p instanceof Professor)
		{
			sql = "UPDATE Professor SET registro = ? WHERE rg = ?";
			ps = c.prepareStatement(sql);
			ps.setString(1, ((Professor) p).getRegistro());
			ps.setString(2, p.getRg());
			ps.execute();
			ps.close();
		}		
		
		String numCelular = "UPDATE Telefone SET numeroTelefone = ?" +
		" WHERE tipoTelefone = 'Celular' AND rg = ?";
		
		ps = c.prepareStatement(numCelular);
		ps.setString(1, p.getNumeroCelular());
		ps.setString(2, p.getRg());
		ps.execute();
		ps.close();
		
		String numResidencia = "UPDATE Telefone SET numeroTelefone = ?" +
				" WHERE tipoTelefone = 'Residencial' AND rg = ?";
		
		ps = c.prepareStatement(numResidencia);
		ps.setString(1, p.getNumeroResidencia());
		ps.setString(2, p.getRg());
		ps.execute();
		ps.close();			
	}
	
	public void deletaPessoa(Pessoa p) throws SQLException{
		
		PreparedStatement ps;
		String sql="";
		
		if (p instanceof AlunoFatec) 
		{
			sql = "DELETE AlunoFatec WHERE rg = ?";
			
			ps = c.prepareStatement(sql);
			ps.setString(1, p.getRg());
			ps.execute();
			ps.close();
		}
		else if (p instanceof AlunoEtec)
		{
			sql = "DELETE AlunoEtec WHERE rg = ?";
			ps = c.prepareStatement(sql);
			ps.setString(1, p.getRg());
			ps.execute();
			ps.close();			
		}
		else if(p instanceof Funcionario)
		{
			sql = "DELETE Funcionario WHERE rg = ?";
			ps = c.prepareStatement(sql);			
			ps.setString(1, p.getRg());
			ps.execute();
			ps.close();				
		}
		else if (p instanceof Professor)
		{
			sql = "DELETE Professor WHERE rg = ?";
			ps = c.prepareStatement(sql);
			ps.setString(1, ((Professor) p).getRegistro());
			ps.setString(2, p.getRg());
			ps.execute();
			ps.close();
		}		
		
		String numCelular = "DELETE Telefone" +
		" WHERE tipoTelefone = 'Celular' AND rg = ?";
		
		ps = c.prepareStatement(numCelular);
		ps.setString(1, p.getRg());
		ps.execute();
		ps.close();
		
		String numResidencia = "DELETE Telefone" +
				" WHERE tipoTelefone = 'Residencial' AND rg = ?";
		
		ps = c.prepareStatement(numResidencia);		
		ps.setString(1, p.getRg());
		ps.execute();
		ps.close();			
		
				
		sql = "Delete Pessoa Where rg = ?";
		ps = c.prepareStatement(sql);
		ps.setString(1, p.getRg());
		ps.execute();
		ps.close();
		
	}	
	
	public Pessoa consultaPessoa(Pessoa p) throws SQLException 
	{		
		String sql="";
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		if (p instanceof AlunoFatec) 
		{
			sql = "SELECT Pessoa.nome,Pessoa.rg,Pessoa.email, Pessoa.complemento, Pessoa.numero, Pessoa.rua"
					+ " FROM Pessoa"
					+ " INNER JOIN AlunoFatec"
					+ " ON AlunoFatec.rg = Pessoa.rg" + " Where ra = ?";

			ps = c.prepareStatement(sql);
			ps.setString(1, ((AlunoFatec) p).getRa());

		} 		
		else if (p instanceof AlunoEtec) 
		{
			sql = "SELECT Pessoa.nome,Pessoa.rg,Pessoa.email, Pessoa.complemento, Pessoa.numero, Pessoa.rua"
					+ " FROM Pessoa"
					+ " INNER JOIN AlunoEtec"
					+ " ON AlunoEtec.rg = Pessoa.rg" + " Where rm = ?";

			ps = c.prepareStatement(sql);
			ps.setString(1, ((AlunoEtec) p).getRm());
		}
		else if(p instanceof Funcionario)
		{
			sql = "SELECT Pessoa.nome,Pessoa.rg,Pessoa.email, Pessoa.complemento, Pessoa.numero, Pessoa.rua, Funcionario.codSetor" +
					" FROM Pessoa" +
					" INNER JOIN Funcionario" +
					" ON Funcionario.rg = Pessoa.rg" +
					" Where Funcionario.rg = ?"; 
			
			ps = c.prepareStatement(sql);
			ps.setString(1, ((Funcionario) p).getRg());
			
			rs = ps.executeQuery();
			
			if (rs.next()) 
			{
			   ((Funcionario) p).setCodSetor(rs.getInt("codSetor"));								
			}		
			
		}
		else if (p instanceof Professor)
		{
			sql = "SELECT Pessoa.nome,Pessoa.rg,Pessoa.email, Pessoa.complemento, Pessoa.numero, Pessoa.rua" +
					" FROM Pessoa" +
					" INNER JOIN Professor" +
					" ON Professor.rg = Pessoa.rg" +
					" Where registro = ?";
			
			ps = c.prepareStatement(sql);
			ps.setString(1, ((Professor) p).getRg());						
		}
		
		rs = ps.executeQuery();
		
		if (rs.next()) 
		{
			p.setNome(rs.getString("Nome"));
			p.setRg(rs.getString("Rg"));
			p.setEmail(rs.getString("email"));
			p.setRua(rs.getString("Rua"));
			p.setNumero(rs.getString("Numero"));
			p.setComplemento(rs.getString("Complemento"));				
		}		
		
		
		String numCelular = "SELECT Telefone.numeroTelefone" +
							" FROM Telefone" +
							" WHERE tipoTelefone = 'Celular' AND rg = ?";
		
		ps = c.prepareStatement(numCelular);
		ps.setString(1, p.getRg());
		rs = ps.executeQuery();
		
		if (rs.next()) 
		{
			p.setNumeroCelular(rs.getString("numeroTelefone"));				
		}		
		
		String numResidencia = "SELECT Telefone.numeroTelefone" +
								" FROM Telefone" +
								" WHERE tipoTelefone = 'Residencial' AND rg = ?";

		ps = c.prepareStatement(numResidencia);
		ps.setString(1, p.getRg());
		rs = ps.executeQuery();
		
		if (rs.next()) 
		{
			p.setNumeroResidencia(rs.getString("numeroTelefone"));				
		}
		
		rs.close();
		ps.close();			
		
		return p;
	}
	
	
		
}
